Auto-generated numeric keys are everywhere in relational databases. In SQL Server, two features dominate this space:
Both generate numbers. Both are fast. Both are widely used. Today, though, IDENTITY columns are by far the most common – yet in our client work, we tend to use SEQUENCE objects almost exclusively. Everything I can do with an IDENTITY column, I can also do with a SEQUENCE object, and we find them more flexible.
As a simple example, if you’ve ever tried to perform a SET IDENTITY_INSERT ON across a linked server, you’d know this doesn’t work. With SEQUENCE, I never need to do this type of operation.
Choosing between these two matters, because they behave differently in ways that affect correctness, scalability, and maintainability.
This guide explains how IDENTITY columns and SEQUENCE objects differ in SQL Server, and how you might decide which one is appropriate for a given design.
What is an IDENTITY column in SQL Server?
An IDENTITY column in SQL Server is a table-level property that automatically generates a value when a row is inserted:
|
1 2 3 4 5 6 |
CREATE TABLE sales.orders ( order_id INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_sales_orders PRIMARY KEY, order_date DATE NOT NULL ); |
Key characteristics:
- Defined inside a table
- Values are generated at insert time
- Tightly coupled to that table
- Automatically incremented
Once created, the IDENTITY behavior is largely implicit. Inserts occur and values are generated automatically.
What is a SEQUENCE column in SQL Server?
A SEQUENCE object is a standalone database object that generates numbers on demand:
|
1 2 3 |
CREATE SEQUENCE sales.order_id START WITH 1 INCREMENT BY 1; |
Used explicitly:
|
1 2 3 |
INSERT INTO sales.orders (order_id, order_date) VALUES (NEXT VALUE FOR sales.order_id, SYSDATETIME()); |
Key characteristics:
- Independent database object
- Values are requested explicitly
- Can be shared across tables
- Behavior is fully configurable
A SEQUENCE behaves more like a service than a column property. And importantly, if you want to override them because you’re moving data around, it’s easy.
Fast, reliable and consistent SQL Server development…
IDENTITY vs SEQUENCE: Ownership and coupling
Ownership and coupling is the most fundamental difference between IDENTITY and SEQUENCE in SQL Server.
IDENTITY
- Belongs to a single table
- Cannot be shared
- Cannot be reused elsewhere
- Tightly bound to the table lifecycle
SEQUENCE
- Independent object
- Can be used by multiple tables
- Can be referenced by code, procedures, and triggers
- Can outlive tables
- Design implication
If the number belongs to the table, IDENTITY often seems to fit naturally. If the number belongs to the business process, a SEQUENCE is often a better abstraction.
As an example, imagine I had a system with tables for CarBookings, HotelBookings, FlightBookings, etc. and I wanted to use a single ID that spanned all these tables. A SEQUENCE object makes this easy, as just one could be the ID generator for all the tables.
IDENTITY vs SEQUENCE: Control and predictability
IDENTITY
- Generated automatically
- Limited control over when values are consumed
- Gaps are normal and unavoidable
- Reseeding affects the entire table
SEQUENCE
- Explicit value requests
- Can generate values before insert
- Can be cached, cycled, and incremented flexibly
- Can be reset or altered independently
Neither option guarantees gap-free numbering. A SEQUENCE simply provides more control over when gaps occur.
IDENTITY vs SEQUENCE: Multi-row and batch inserts
Here, behavior between the two diverges in subtle but important ways:
IDENTITY
- Values assigned during insert
- Ordering may not be obvious under parallel plans
- Assignment order is harder to predict in bulk loads
SEQUENCE
- Values requested explicitly
- Values can be generated ahead of time
- Deterministic ordering is easier to achieve when required
For systems that rely on explicit ordering or pre-allocation, sequences offer clearer semantics.
IDENTITY vs SEQUENCE: Replication, synchronization, distribution
IDENTITY
- Historically difficult with replication
- Requires identity ranges or careful configuration
- Risk of collisions if mismanaged
SEQUENCE
- Designed for distributed scenarios
- Supports caching to reduce contention
- Easier to coordinate across nodes or services
This is one of the primary reasons SEQUENCE objects exist. They solve problems that IDENTITY columns were never designed to handle.
IDENTITY vs SEQUENCE: Inserts from multiple code paths
Common insert paths include:
- Direct inserts
- Stored procedures
- Triggers
- Extract, Transform, Load (ETL) processes
- Service-based inserts
With IDENTITY, they are:
- Always generated implicitly
- Harder to intercept or customize
- Requires special syntax to override (
SET IDENTITY_INSERT)
And with SEQUENCE, they:
- Can be used anywhere
- Can be called conditionally
- Can be reused consistently across paths
When multiple systems need to generate compatible identifiers, a SEQUENCE object provides a cleaner contract.
Enjoying this article? Subscribe to the Simple Talk newsletter
IDENTITY vs SEQUENCE: Performance and scalability
In most real-world systems, both options scale well and are extremely fast. Neither should be chosen solely for performance reasons
At very high insert rates:
- Sequences with caching can reduce contention
- By default, identity contention can appear in extreme write-heavy workloads
These differences matter at scale, but they are rarely the primary design driver.
What can you control for IDENTITY in modern SQL Server?
SQL Server 2017+ lets you turn identity value caching ON or OFF per database using the database-scoped configuration IDENTITY_CACHE. You can also disable IDENTITY pre-allocation and instance-wide using trace flag 272 (global scope).
What you CANNOT control for IDENTITY in SQL Server
You cannot set the cache size for an IDENTITY column. With IDENTITY, it’s essentially ON/OFF, not tune the cache amount. (The internal pre-allocation amount varies by version/type/implementation, but it’s not user-configurable.)
IDENTITY vs SEQUENCE: Common anti-patterns
Using IDENTITY as a business identifier:
Ideally, you would avoid coupling business meaning to storage behavior, which is brittle and hard to change.
Assuming either IDENTITY or SEQUENCE is gap-free:
Deletes, rollbacks, restarts, and crashes all introduce gaps.
If gap-free numbering is required, neither IDENTITY or SEQUENCE is sufficient.
How to choose IDENTITY or SEQUENCE in SQL Server
Choose IDENTITY when:
- The value is purely a surrogate key
- The value belongs only to the table
- Minimal configuration is desired
- Gaps and reuse are not a concern
Choose SEQUENCE when:
- The value represents a business concept
- Multiple tables or processes need numbers
- Explicit control over generation is required
- Reuse, coordination, or distribution matters
- You need to insert across linked services
- You are working in more complex environments
In conclusion: IDENTITY vs SEQUENCE in SQL Server
IDENTITY columns are simple, convenient, and often sufficient. Despite this, we tend to use SEQUENCE objects all the time nowadays. They’re explicit, flexible, and better suited to complex systems – ideal for the mixed environments that we often find ourselves working in.
FAQs: IDENTITY vs SEQUENCE in SQL Server
1. What’s the difference between IDENTITY and SEQUENCE in SQL Server?
IDENTITY is a table property that auto-generates values on insert. SEQUENCE is a standalone object that generates numbers on demand and can be shared.
2. Which should I use for primary keys in SQL Server?
Use IDENTITY for simple, table-only surrogate keys. Use SEQUENCE when values must be shared, coordinated, or controlled explicitly.
3. Are IDENTITY or SEQUENCE gap-free in SQL Server?
No. Both can produce gaps due to rollbacks, crashes, and caching.
4. Which is better for distributed systems in SQL Server?
SEQUENCE objects are better suited for multi-table and distributed scenarios.
Load comments